package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.Discipline;
import pos.domain.DisciplineImpl;
import util.UIDGenerator;

public class JDBCDisciplineDAO implements IDisciplineDAO {
	
	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCDisciplineDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}

	
	public List<Discipline> getAllDisciplines() {

		String sql = "SELECT * FROM discipline";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Discipline> listDiscipline = null;

		try {
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();
			listDiscipline = new ArrayList<Discipline>();

			while (result.next()) {
				Discipline discipline = new DisciplineImpl();
				discipline.setId(result.getString("iddiscipline"));
				discipline.setName(result.getString("name"));
//				discipline.setCategory(new JDBCCategoryDAO().getCategoryById(result.getString("idcategory")));
				
				listDiscipline.add(discipline);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					conn.close();
				}
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}
		}

		return listDiscipline;
	}

	public Discipline getDisciplineById(String idDiscipline) {

		Discipline discipline = new DisciplineImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Discipline with that ID.
		String sql = "SELECT * FROM discipline WHERE (iddiscipline = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idDiscipline);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()) {
				discipline.setId(result.getString("iddiscipline"));
				discipline.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return discipline;
	}
	
	public Discipline getDisciplineByEvent(String idEvent) {

		Discipline discipline = new DisciplineImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Discipline with that ID.
		String sql = "SELECT * FROM discipline WHERE (idevent = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvent);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()) {
				discipline.setId(result.getString("iddiscipline"));
				discipline.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return discipline;
	}

	public Discipline getDisciplineByName(String name) {

		Discipline discipline = new DisciplineImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Discipline with that name.
		String sql = "SELECT * FROM discipline WHERE (name = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()) {
				discipline.setId(result.getString("iddiscipline"));
				discipline.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return discipline;
	}
	
	public Discipline getDisciplineByCategory(String idCategory) {

		Discipline discipline = new DisciplineImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Discipline with that ID.
		String sql = "SELECT * FROM discipline WHERE (idcategory = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idCategory);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()) {
				discipline.setId(result.getString("iddiscipline"));
				discipline.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return discipline;
	}

	public Discipline insertDiscipline(Discipline discipline, String idCategory) {

		PreparedStatement stmt = null;
		String idDiscipline = "";
		if(discipline.getId()==null){
			idDiscipline = UIDGenerator.getInstance().getKey(); // ID for DB
			discipline.setId(idDiscipline); // ID for object
		}
		idDiscipline = discipline.getId();
		discipline.setId(idDiscipline);

		String sql = "INSERT INTO discipline (iddiscipline,name,idcategory) VALUES (?,?,?)";

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, idDiscipline);
			stmt.setString(2, discipline.getName());
			stmt.setString(3, idCategory);
//			stmt.setString(4, idEvent);

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();

		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return discipline;
	}

	public void removeDiscipline(Discipline discipline) {

		PreparedStatement stmt = null;

		String sql = "DELETE FROM discipline WHERE (iddiscipline = ?)";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, discipline.getId());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (Exception e) {

			}
		}
		
	}

	public boolean existDiscipline(String name) {

		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM discipline WHERE (name = ?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			result = stmt.executeQuery();

			if (result.next()==false) {
				res = false;
			} else {
				res = true;
		}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;
	}

}
